﻿Imports sonicERPinfo
Imports System.Data.SqlClient

Public Class menuDAL
    Dim dal As New DAL

    Public Function lista()
        Dim select_ As String = "SELECTMENU"


        Dim parameter As New ArrayList

        Dim entidades As New ArrayList

        Return dal.SqlDataAdapter(select_, True, parameter, entidades, False)

    End Function


    Public Sub insert(ByVal menu As Menu)

        Try

            Dim select_ As String = "INSERTMENUNIVEL"
            Dim parameter As New ArrayList
            parameter.Add("@idmenu")
            parameter.Add("@visible")
            Dim entidade As New ArrayList
            entidade.Add(menu.idmenu)
            entidade.Add(menu.visible)
            dal.SQLExecuteNonQuery(select_, True, parameter, entidade, True)
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
    End Sub

    Public Sub insert1(ByVal menu As Menu)

        Try

            Dim select_ As String = "DECLARE @idnivelusuario INT " +
   " SET @idnivelusuario =(SELECT MAX(id) FROM  nivelUsuario) " +
   " INSERT INTO menunivel1 " +
   "  (idmenu, idnivel,idgrupo,visible) " +
   "  VALUES  (@idmenu,@idnivelusuario,@idgrupo,@visible)"

            Dim parameter As New ArrayList
            parameter.Add("@idmenu")
            parameter.Add("@idgrupo")
            parameter.Add("@visible")
            Dim entidade As New ArrayList
            entidade.Add(menu.idmenu)
            entidade.Add(menu.idgrupo)
            entidade.Add(menu.visible)
            dal.SQLExecuteNonQuery(select_, False, parameter, entidade, True)
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
    End Sub

    Public Sub update1(ByVal menu As Menu)
        Try

            Dim select_ As String = "UPDATE    menunivel1 " +
   " SET  visible = @visible" +
   " WHERE (idmenu = @idmenu) AND (idnivel = @idnivel)"

            Dim parameter As New ArrayList
            parameter.Add("@visible")
            parameter.Add("@idnivel")
            parameter.Add("@idmenu")
            Dim entidade As New ArrayList
            entidade.Add(menu.visible)
            entidade.Add(menu.idnivel)
            entidade.Add(menu.idmenu)
            dal.SQLExecuteNonQuery(select_, False, parameter, entidade, True)
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
    End Sub


    Public Sub update(ByVal menu As Menu)
        Try

            Dim select_ As String = "UPDATEMENUNIVEL"
            Dim parameter As New ArrayList
            parameter.Add("@visible")
            parameter.Add("@idnivel")
            parameter.Add("@idmenu")
            Dim entidade As New ArrayList
            entidade.Add(menu.visible)
            entidade.Add(menu.idnivel)
            entidade.Add(menu.idmenu)
            dal.SQLExecuteNonQuery(select_, True, parameter, entidade, True)
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
    End Sub

    Public Sub delete(ByVal menu As Menu)
        Try
            Dim select_ As String = "DELETEMENU"
            Dim parameter As New ArrayList
            parameter.Add("@idmenu")
            parameter.Add("@idnivel")

            Dim entidade As New ArrayList
            entidade.Add(menu.idmenu)
            entidade.Add(menu.idnivel)

            dal.SQLExecuteNonQuery(select_, True, parameter, entidade, True)
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
    End Sub

    Public Sub delete1(ByVal menu As Menu)
        Try
            Dim select_ As String = "DELETE FROM menunivel1 " &
           "  WHERE   (idmenu = @idmenu) AND (idnivel = @idnivel) "

            Dim parameter As New ArrayList
            parameter.Add("@idmenu")
            parameter.Add("@idnivel")

            Dim entidade As New ArrayList
            entidade.Add(menu.idmenu)
            entidade.Add(menu.idnivel)

            dal.SQLExecuteNonQuery(select_, False, parameter, entidade, True)
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
    End Sub

    Public Sub lista1(ByVal menu As Menu)
        Try

            Dim con As New conexao

            Dim select_ As String = "SELECTTRUE"
            Dim cn As New SqlConnection(con.conexao)

            Dim cmd As New SqlCommand
            cmd.Connection = cn
            cmd.CommandText = select_
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@idnivel", menu.idnivel)
            cmd.Parameters.AddWithValue("@idmenu", menu.idmenu)
            cn.Open()
            Dim dr As SqlDataReader = cmd.ExecuteReader

            If dr.Read Then
                menu.visible = Convert.ToBoolean(dr("visible").ToString)
            End If
            cn.Close()
            dr.Close()
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try

    End Sub

    Public Function getmenu(ByVal tabela As sonicERPinfo.Menu) As DataView
        Try



            Dim select_ As String = "SELECT " +
            " M.visible " +
           " FROM Usuario U INNER JOIN menunivel1 M " +
           "  ON U.idnivel = M.idnivel " +
           " WHERE " +
            " U.apelido  = @usuario "

            Dim parameter As New ArrayList
            parameter.Add("@usuario")

            Dim entidade As New ArrayList
            entidade.Add(tabela.usuario)
            Return dal.SqlDataAdapter(select_, False, parameter, entidade, True)
        Catch ex As SqlException
            Throw New Exception(ex.Message)
        End Try
    End Function

    Public Function getmenu1(ByVal tabela As Menu) As DataTable
        Dim select_ As String = "SELECT * FROM Menugrupos ORDER BY 1"

        Dim paramater As New ArrayList
        Dim entidade As New ArrayList
        Dim dv As DataView = dal.SqlDataAdapter(select_, False, paramater, entidade, False)
        Dim dt As DataTable = dv.ToTable()
        Return dt
    End Function

    Public Function getmenugrupo(ByVal tabela As Menu) As DataTable
        Dim select_ As String = "SELECT  * from menu1"
        Dim parameter As New ArrayList
        Dim entidade As New ArrayList
        Dim dv As DataView = dal.SqlDataAdapter(select_, False, parameter, entidade, False)
        Dim dt As DataTable = dv.ToTable()
        Return dt
    End Function

    Public Function menumasterpage(ByVal tabela As Menu)
        Dim select_ As String = "SELECT Me.id, Me.menu, me.idgrupo , Me.pagina" &
          " FROM MENUNIVEL1 M INNER JOIN MENU1 me ON	m.idmenu = me.id WHERE idnivel=@idnivel AND m.visible = 1"
        Dim parameter As New ArrayList
        parameter.Add("@idnivel")
        Dim entidade As New ArrayList
        entidade.Add(tabela.idnivel)
        Dim dv As DataView = dal.SqlDataAdapter(select_, False, parameter, entidade, True)
        Dim dt As DataTable = dv.ToTable()
        Return dt

    End Function

    Public Function getmenvisible(ByVal tabela As Menu)
        Try

            Dim select_ As String = " SELECT  visible   FROM menunivel1  WHERE idnivel = @idnivel"
            Dim paramenter As New ArrayList
            paramenter.Add("@idnivel")
            Dim entidade As New ArrayList
            entidade.Add(tabela.idnivel)
            Dim dv As DataView = dal.SqlDataAdapter(select_, False, paramenter, entidade, True)
            Dim dt As DataTable = dv.ToTable()
            Return dt
        Catch ex As SqlException
            Throw New Exception(ex.Message)
        End Try
    End Function
End Class
